package jech913.basic;

import java.io.IOException;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class JoinMemberV2
 */
@WebServlet(urlPatterns={"/member/new","/member/newOk",
		    "/member/readAll","/member/readOne",
		    "/member/modify","/member/modifyOk",
		    "/member/remove"})
public class JoinMemberV2 extends HttpServlet {
	private static final long serialVersionUID = 1L;
	private static final String DRV="oracle.jdbc.OracleDriver";
	private static final String URL="jdbc:oracle:thin:@//112.216.253.122:1521/xe";
	private static final String USR="jech913";
	private static final String PWD="jech913";
	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	
	//추가, 전체 및 상세 조회, 수정, 삭제
	protected void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
		// TODO Auto-generated method stub
		String uri = req.getRequestURI();
		System.out.println(req.getRequestURI());
		
		//추가
		if(uri.endsWith("member/new")){
			JoinMemberV2Form(req,res);
		}
		//전체조회 
		else if(uri.endsWith("member/readAll")){
			selectAllMemberV2(req,res);
		}
		//상세조회 
		else if(uri.endsWith("member/readOne")){
			selectOneMemberV2(req,res);
		}
	}

	

	private void selectAllMemberV2(HttpServletRequest req,
			HttpServletResponse res) throws IOException {
		String SQL="select mno, mname, mid, mail from memberv2 order by mno desc";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder sb = null;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(SQL);
			rs = ps.executeQuery();
			sb = new StringBuilder();
			
			while(rs.next()){
				sb.append("<div>")
				  .append(rs.getString(1)).append("")
				  .append("<a href='./readOne?mno=")
				  .append(rs.getString(1)).append("'>")
				  .append(rs.getString(2)).append("</a>")
				  .append(rs.getString(3)).append("")
				  .append(rs.getString(4)).append("</div>");
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(rs!=null)try{rs.close();}catch(Exception e){}
			if(ps!=null)try{ps.close();}catch(Exception e){}
			if(conn!=null)try{conn.close();}catch(Exception e){}
		}
		
		res.setContentType("text/html;charset=UTF-8");
		PrintWriter out = res.getWriter();
		
		out.println("<!DOCTYPE html>");
		out.print("<html>");
		out.print("<head><title> 회원 전체조회 </title></head>");
		out.print("<body><h1> 회원 전체조회</h1>");
		out.print(sb.toString());

		out.print("</body>");
		out.print("</html>");
		
	}

	private void selectOneMemberV2(HttpServletRequest req,
			HttpServletResponse res) throws IOException {
		// TODO Auto-generated method stub
		String SQL="select mno,mname, mid, mail from memberv2 where mno=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuilder sb = null;
		
		String mno = req.getParameter("mno");
		
		try{
			
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(SQL);
			ps.setString(1, mno);
			rs = ps.executeQuery();
			sb = new StringBuilder();
			
			if(rs.next()){
				sb.append("<div>")
				  .append("번호:").append(rs.getString(1))
				  .append("이름:").append(rs.getString(2))
				  .append("아이디:").append(rs.getString(3))
				  .append("이메일:").append(rs.getString(4)).append("</div>");
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(rs!=null)try{rs.close();}catch(Exception e){}
			if(ps!=null)try{ps.close();}catch(Exception e){}
			if(conn!=null)try{conn.close();}catch(Exception e){}
		}
		res.setContentType("text/html;charset=UTF-8");
		PrintWriter out = res.getWriter(); 
		
		out.println("<!DOCTYPE html>");
		out.print("<html>");
		out.print("<head><title> 성적 상세조회 </title></head>");
		out.print("<body><h1> 성적 상세조회</h1>");
		out.print(sb.toString());

		out.print("</body>");
		out.print("</html>");
		
	}
	private void JoinMemberV2Form(HttpServletRequest req,
			HttpServletResponse res) throws IOException {
		// TODO Auto-generated method stub
		res.setContentType("text/html;charset=UTF-8");
		PrintWriter out = res.getWriter();
		
		out.print("<!DOCTYPE html>");
		out.print("<html>");
		out.print("<head><title> 회원가입 프로그램 </title></head>");		
		out.print("<body><h2> 회원가입 </h2>");
		out.print("<form name='join' method='post' action='./newOk'>");
		out.print("<div> 이름: <input type='text' name='name'> </div>");
		out.print("<div> 아이디: <input type='text' name='userid'> </div>");
		out.print("<div> 비밀번호: <input type='password' name='pwd'> </div>");
		out.print("<div> 비밀번호 확인: <input type='password' name='repwd'> </div>");
		out.print("<div> 이메일: <input type='email' name='mailaddr'> </div>");
		out.print("<div> <button type='submit'> 입력완료 </div>");
		out.print("<button type='reset'> 다시입력 </button></div>");		
		out.print("<div><p><a href='./readAll'>회원조회</a></div>");		
		
		out.print("</form>");
		out.print("</body>");
		out.print("</html>");
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	//추가완료, 수정완료 
	protected void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
		// TODO Auto-generated method stub
		
		String uri=req.getRequestURI();
		System.out.println(uri);

		if(uri.endsWith("member/newOk")){
			insertMemberV2(req, res);
		}
	}

	private void insertMemberV2(HttpServletRequest req, HttpServletResponse res) throws IOException {
		// TODO Auto-generated method stub
		
		String SQL="insert into memberv2(mno, mname, mid, pwd,mail) values(mno.nextval, ?, ?, ?,?)";
		Connection conn = null;
		PreparedStatement ps = null;
		
		req.setCharacterEncoding("UTF-8");
		String name = req.getParameter("name");
		String id = req.getParameter("userid");
		String pwd = req.getParameter("pwd");
		String repwd = req.getParameter("repwd");
		String email = req.getParameter("mailaddr");
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(SQL);
			ps.setString(1, name);
			ps.setString(2, id);
			ps.setString(3, pwd);
			ps.setString(4, email);
			
			int cnt = ps.executeUpdate();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(ps!=null)try{ps.close();}catch(Exception e){}
			if(conn!=null)try{conn.close();}catch(Exception e){}
		}
		res.setContentType("text/html;charset=UTF-8");
		PrintWriter out = res.getWriter();
		
		out.println("<!DOCTYPE html>");
		out.print("<html>");
		out.print("<head><title> 성적 입력결과 </title></head>");
		out.print("<body><h1> 성적 입력결과</h1>");

		out.print("<div> 이름:"+name+"</div>");
		out.print("<div> 아이디:"+id+"</div>");
		out.print("<div> 비밀번호:"+pwd+"</div>");
		out.print("<div> 메일:"+email+"</div>");
		//목록(SungJukV4ReadAll로 이동 )
		out.print("<div><p> <a href='./readAll'>목록으로 이동 </a></p></div>");
		out.print("<div><p> <a href='./readOne'>상세조회로 이동 </a></p></div>");
		out.print("</body>");
		out.print("</html>");
		
	}

}
